package com.execl.utils;


import com.execl.pojo.Execl;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.*;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;

/**
 * @Description 实体类生成sql语句
 **/

@Component
@Slf4j
public class SQLTableUtil {

    @Value("${server.port}")
    private String port;

    @Value("${spring.datasource.driverClassName}")
    private String driverClassName;

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;


    @Value("${spring.datasource.password}")
    private String password;


    public static Map<String, String> javaProperty2SqlColumnMap = new HashMap<>();

    final static String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    //指定连接数据库的url
    final static String DB_URL = "jdbc:mysql://127.0.0.1:3306/cloud?characterEncoding=UTF8";
    //mysql用户
    final static String name = "root";
    //mysql密码
    final static String pwd = "";

    /*
     * 类型装换
     */
    static {
        javaProperty2SqlColumnMap.put("Integer", "INTEGER");
        javaProperty2SqlColumnMap.put("Short", "tinyint");
        javaProperty2SqlColumnMap.put("Long", "bigint");
        javaProperty2SqlColumnMap.put("BigDecimal", "decimal(19,2)");
        javaProperty2SqlColumnMap.put("Double", "double precision not null");
        javaProperty2SqlColumnMap.put("Float", "float");
        javaProperty2SqlColumnMap.put("Boolean", "bit");
        javaProperty2SqlColumnMap.put("Timestamp", "datetime");
        javaProperty2SqlColumnMap.put("String", "VARCHAR(255)");
        javaProperty2SqlColumnMap.put("long", "INTEGER(100)");
        javaProperty2SqlColumnMap.put("int", "INTEGER(100)");
        javaProperty2SqlColumnMap.put("boolean", "bit");
        javaProperty2SqlColumnMap.put("Date", "Date");
    }


    public static void main(String[] args) throws IOException {
        String sql = createTable(Execl.class, "user");
        CreatTables(sql);
    }


    /**
     * 返回sql语句
     *
     * @param obj       实体类
     * @param tableName 表名
     * @return
     * @throws IOException
     */
    public static <T> String createTable(Class<T> obj, String tableName) throws IOException {
        Field[] fields = null;
        fields = obj.getDeclaredFields();
        String param = null;
        String column = null;
        StringBuilder sb = null;
        sb = new StringBuilder(50);
        if (tableName == null || tableName.equals("")) {
            //未传表明默认用类名
            tableName = obj.getName();
            tableName = tableName.substring(tableName.lastIndexOf(".") + 1);
        }
        sb.append("create table ").append(tableName).append(" ( \r\n");
        boolean firstId = true;
        File file = null;
        for (Field f : fields) {
            column = f.getName();
            if (column.equals("serialVersionUID")) {
                continue;
            }
            param = f.getType().getSimpleName();
            sb.append(column);//一般第一个是主键
            sb.append(" ").append(javaProperty2SqlColumnMap.get(param)).append(" ");
            if (firstId) {//类型转换
                sb.append(" PRIMARY KEY ");
                firstId = false;
            }
            Annotation[] allAnnotations = f.getAnnotations();
            sb.append(",\n ");
        }
        String sql = null;
        sql = sb.toString();
        //去掉最后一个逗号
        int lastIndex = sql.lastIndexOf(",");
        sql = sql.substring(0, lastIndex) + sql.substring(lastIndex + 1);
        sql = sql.substring(0, sql.length() - 1) + " )ENGINE =INNODB DEFAULT  CHARSET= utf8;\r\n";
        System.out.println(sql);
        return sql;
    }

    /**
     * 执行sql语句
     *
     * @param sql
     * @return
     */
    public static void CreatTables(String sql) {
        Connection conn = null;
        Statement stmt = null;
        try {
            //注册jdbc驱动
            Class.forName(JDBC_DRIVER);
            //打开连接
            conn = DriverManager.getConnection(DB_URL, name, pwd);

            //执行创建表
            stmt = conn.createStatement();

            if (0 == stmt.executeLargeUpdate(sql)) {
                System.out.println("成功创建表！");
            } else {
                System.out.println("创建表失败！");
            }
            stmt.close();
            conn.close();
        } catch (Exception e) {
            System.out.println("创建表失败！");
            e.printStackTrace();
        }
    }


    // 插入数据库
    public void insertData() throws SQLException, ClassNotFoundException {
        Connection connection =  getConnection();;
        String sql = "INSERT INTO `cloud`.`test`(`id`, `title`, `apply_date`, `applicant_ch`, `applicant_en`, `address_ch`, `agency`, `latest_status`, `options_start`, `options_end`, `update`) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?, ?,?)";
        long start = System.currentTimeMillis();
        try {
           if (connection != null){
               System.out.println("连接成功:  "+connection);
           }
            connection.setAutoCommit(false);
            PreparedStatement ptatm = connection.prepareStatement(sql);

            List<Execl> execlList = getFileList();
            System.out.println("总共读取sql文件中" + execlList.size() + "条数据");

            int i = 0;
            try {
                long Temp = System.currentTimeMillis();
                //将内存中的数据读取出来，准备批量写入数据库
                for (Execl execl : execlList) {

                    assert false;
                    ptatm.setLong(1, execl.getId());                  //id
                    ptatm.setString(2, execl.getTitle());              //商标名称
                    ptatm.setLong(3, execl.getApply_date());          //申请日期
                    ptatm.setString(4, execl.getApplicant_ch());      //申请人名称中文
                    ptatm.setString(5, execl.getApplicant_en());      //申请人名称英文
                    ptatm.setString(6, execl.getAddress_ch());      //申请人地址中文
                    ptatm.setString(7, execl.getAgency());          //代理/办理机构
                    ptatm.setInt(8, execl.getLatest_status());      //最新商标状态
                    ptatm.setLong(9, execl.getOptions_start());      //专用权期限开始时间
                    ptatm.setLong(10, execl.getOptions_end());      //专用权期限结束时间
                    ptatm.setLong(11, execl.getUpdate());              //修改日期
                    ptatm.addBatch();    //批量记录到容器里

                    if (i == 100000) {    //当数据读取到10w条则把这部分数据先写入数据库
                        i = 0;        //重置 i 计数器
                        ptatm.executeBatch();      //执行批量SQL语句，该语句可能返回多个结果
                        ptatm.clearBatch();        //清除容器中已写入的数据,预备下次存入数据使用

                        long l = System.currentTimeMillis();
                        System.out.println("分段插入耗时:  "+(l-Temp)/1000);
                        Temp = l;
                    }
                    i++;
                    //System.out.println("内存中读取数据条数" + i);
                }
                ptatm.executeBatch();
                ptatm.close();
                connection.commit();
            } catch (Exception e) {
                ptatm.close();
                connection.commit();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            connection.close();
        }
        System.out.println("插入数据耗时:  "+(System.currentTimeMillis()-start)/1000);
    }


    // 连接数据库
    public Connection getConnection() throws ClassNotFoundException, SQLException {
        Connection connection = null;
        try {
            Class.forName(driverClassName);
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.getMessage();
        }
        return connection;
    }

    // 处理TXT文件表
    public static List<Execl> getFileList() {
        String inputFile = "G:\\LaoXu\\下载\\1665846151.4327369\\test.sql"; //大文件路径 测试时候是300M的txt文件
        List<Execl> execlList = new ArrayList<>();
        try {
            // 当逐行读写大于2G的文本文件时推荐使用以下代码
            BufferedInputStream bis = new BufferedInputStream(new FileInputStream(new File(inputFile)));
            BufferedReader in = new BufferedReader(new InputStreamReader(bis, StandardCharsets.UTF_8), 10 * 1024 * 1024);// 10M缓存

            while (in.ready()) {
                String line = in.readLine();

                String substring = line.substring(27);
                String[] split = substring
                        .replace(")", "")
                        .replace(";", "")
                        .split("', '");
                split[0] = split[0].replace("'", "");
                split[split.length - 1] = split[split.length - 1].replace("'", "");
                try {
                    execlList.add(TransBeanUtils.getMappingPo(Execl.class, split));
                } catch (Exception e) {
                    log.info("错误数据:  " + Arrays.toString(split));
                }
            }
            in.close();                        //关闭文件
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return execlList;
    }

}

